package isae.lib;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 *
 * @author admin
 */
public class InitialQuerys {

    public boolean debug = false;
    Connection connection;

    public InitialQuerys() {
    }

    public String avoirNom(String no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select prenom,pere,nom from Etudiant where Nodossier=" + "\"" + no + "\";";

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1) + " " + records.getString(2) + " " + records.getString(3);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirNomInverse(String no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select prenom,pere,nom from Etudiant where Nodossier=" + "\"" + no + "\";";

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(3) + " " + records.getString(1) + " " + records.getString(2);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirNom1(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select prenom,pere,nom from Etudiant where NoEtud=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1) + " " + records.getString(2) + " " + records.getString(3);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirNoDossier(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select Nodossier from Etudiant where NoEtud=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirNoDossierP(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NodossierP from Etudiant where NoEtud=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public int avoirNoEtud(String doss) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NoEtud from Etudiant where Nodossier=" + "\"" + doss + "\"";

        ResultSet records;
        int no = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                no = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return no;
    }

    public int avoirAnneeCour() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select annee from SemestreCourant";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public int avoirSemestreCour() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select semestre from SemestreCourant";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public String avoirNomSpec(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select specialite from Specialite where spec=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public int avoirNoSpec(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select spec from Specialite where anfin is null and Nochef=" + no;
        ResultSet records;
        int s = 0;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                s = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return s;
    }

    public String avoirChef(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select Prenomch, Nomch from Chef where Nochef=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1) + " " + records.getString(2);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirProfesseur(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select prenomprof, nomprof from Professeur where numprof=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(2) + " " + records.getString(1);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirProfesseurInverse(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select prenomprof, nomprof from Professeur where numprof=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1) + " " + records.getString(2);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirEmploye(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select PrenomEmp, NomEmp from Employe where NoEmploye=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1) + " " + records.getString(2);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirIntitule(int nomat) throws ClassNotFoundException {
        String str = "";
        Statement stmt;
        String requete = "select intitule from Matiere ";
        requete += "where Nomatiere=" + nomat;

        ResultSet records;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }

        return str;
    }

    public String avoirCodeCnam(int nomat) throws ClassNotFoundException {
        String str = "";
        Statement stmt;
        String requete = "select cnam from Matiere ";
        requete += "where Nomatiere=" + nomat;

        ResultSet records;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public int avoirCredit(int nomat) throws ClassNotFoundException {

        int cr = 0;
        Statement stmt;
        String requete = "select credit from Matiere ";
        requete += "where Nomatiere=" + nomat;

        ResultSet records;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                cr = records.getInt(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }

        return cr;
    }

    public int avoirNomatiere(String cnam) throws ClassNotFoundException {

        int no = 0;
        Statement stmt;
        String requete = "select Nomatiere from Matiere ";
        requete += "where cnam=" + "\"" + cnam + "\"";

        ResultSet records;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                no = records.getInt(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }

        return no;
    }

    public String avoirSexe(String nodoss) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select sexe from Etudiant where Nodossier=" + "\"" + nodoss + "\"";

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public int avoirAnneeOuverture() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select annee from OuvCourante";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public int avoirOuvertureCourante() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NoOuvertureT from OuvCourante";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public int avoirInscCourante() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select insc from OuvCourante";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public int avoirAnneePeriode() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select annee from PeriodeCourante";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public int avoirPeriodeCourante() throws ClassNotFoundException {
        Statement stmt;
        String requete = "select PeriodeCour from PeriodeCourante";

        ResultSet records;
        int x = 0;

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                x = records.getInt(1);

            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return x;
    }

    public String avoirCentre(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NomCentre from Centre where IdCentre=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirCentreArabe(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select NomCentreA from Centre where IdCentre=" + no;

        ResultSet records;
        String str = "";

        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public int avoirCentreEmploye(int no) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select IdCentre from Employe where NoEmploye=" + no;
        ResultSet records;
        int c = 0;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                c = records.getInt(1);
            }
            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return c;
    }

    public String AvoirCnam(int nomat) throws NamingException, SQLException {

        String cnam = null;
        Statement stmt;
        String requete = "select * from Matiere ";
        requete += "where Nomatiere=" + nomat;
        ResultSet rec;
        try {
            connection = getJdbcIsae().getConnection();
            stmt = connection.createStatement();
            rec = stmt.executeQuery(requete);
            if (rec.first()) {
                cnam = rec.getString("cnam");
            /*mat = new Matiere(rec.getInt("Nomatiere"), rec.getString("intitule"), rec.getString("cnam"),rec.getString("substitution"));
            if(debug){
            System.out.println(mat.getNomatiere()+"/"+mat.getIntitule()+"/"+mat.getCnam()+"/"+mat.getSubstitution());
            }*/
            }


        } catch (SQLException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            if (debug) {
                System.out.println(ex.getLocalizedMessage());
            }
        }
        connection.close();
        return cnam;
    }

    public Etudiant AvoirEtudiant(int NoEtud) throws SQLException {

        Etudiant etud = null;
        try {

            connection = getJdbcIsae().getConnection();
            //    connection = getJdbcIsae().getConnection();
            PreparedStatement ps = connection.prepareStatement("select * from Etudiant where NoEtud=" + NoEtud);
            ResultSet rs = ps.executeQuery();
            if (rs.first()) {
                // public Etudiant(int NoEtud, String Nodossier, String nom, String prenom, String pere, Date DateNaissance, int insc)
                etud = new Etudiant(rs.getInt("NoEtud"), rs.getString("Nodossier"), rs.getString("nom"), rs.getString("prenom"), rs.getString("pere"), rs.getDate("dateNaiss"));
                etud.setInsc(rs.getInt("insc"));
                if (debug) {
                    System.out.println("avoirEtudiant=" + rs.getInt("NoEtud") + rs.getString("Nodossier") + rs.getString("nom") + rs.getString("prenom") + rs.getString("pere") + rs.getDate("dateNaiss") + rs.getInt("insc"));
                }
            }

        } catch (NamingException ex) {
            if (debug) {
                System.out.println("avoir etudiant/excep:" + ex.getLocalizedMessage());
            }
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        connection.close();
        return etud;

    }

    public int InscFoisAvantCourant(int NoEtud) throws NamingException, SQLException, ClassNotFoundException {

        int n = 0;
        Statement stmt;
        String query = "SELECT count(*) from Inscription as t1,Fiche as t2 where t1.code_etudier=t2.code_etudier ";
        query += " and t1.NoEtud=" + NoEtud + " and t1.annee<" + avoirAnneeCour() + " and (t2.payee=1 or t2.dispense=1)";

        ResultSet rec;
        try {
            connection = getJdbcIsae().getConnection();
            stmt = connection.createStatement();
            rec = stmt.executeQuery(query);

            if (rec.first()) {

                n = rec.getInt("count(*)");
                if (debug) {
                    System.out.println("inscfois avand courant,n=" + n);
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            if (debug) {
                System.out.println(ex.getLocalizedMessage());
            }
            n = -1;
        }
        connection.close();
        return n;
    }

    public boolean SiAncienEtd(int NoEtud) throws SQLException, ClassNotFoundException {

        boolean res = false;
        int insc = 0;
        int anneecour = 0;
        int n = 0;//fois insc avant date courant
        insc = AvoirEtudiant(NoEtud).getInsc();
        anneecour = avoirAnneeCour();

        if (insc != 0 && anneecour != 0) {
            if (insc < anneecour - 1) {
                res = true;//l'etud est ancien
                if (debug) {
                    System.out.println("SiAncienETd-insc" + insc + "<annecourant" + anneecour + ",res:true");
                }

            } else {
                try {
                    n = InscFoisAvantCourant(NoEtud);
                    //si il est inscrit avant la date courant
                    if (n >= 1) {
                        res = true;//donc l'etud est ancien
                    } else {
                        res = false;//etd est nouveau
                    }
                } catch (NamingException ex) {
                    if (debug) {
                        System.out.println("SiAncien Exc: " + ex.getLocalizedMessage());
                    }
                    Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }//fin de if anne,insc not zero
        if (debug) {
            if (res) {
                System.out.println("SiAncienEtd l'etud est ancien,res:true");
            } else {
                System.out.println("SiAncienEtd l'etud est nouveau res:false");
            }
        }
        return res;

    }

    public int AvoirMatierePrix(int nomat, int inscfois, boolean ancienEtd) throws NamingException, SQLException {

        int prix = 0;
        if (nomat != 0) {
            int n = inscfois;
            boolean siancienetd = ancienEtd;
            int prixm = 0;
            int memoire = 0;
            boolean prixfixe = false;
            int creditf = 0;
            int niveau = 0;
            Statement stmt;
            String requete = "select * from Matiere ";
            requete += "where Nomatiere=" + nomat;
            ResultSet rec;
            if (debug) {
                System.out.println("avoirMatierePrix()-Debut: parametre--siancien etudiant :" + siancienetd + "inscfois" + n);
            }

            try {
                connection = getJdbcIsae().getConnection();
                stmt = connection.createStatement();
                rec = stmt.executeQuery(requete);
                if (rec.first()) {
                    prixfixe = rec.getBoolean("prixfixe");
                    prixm = rec.getInt("prixm");
                    memoire = rec.getInt("memoire");
                    niveau = rec.getInt("niveau");
                    creditf = rec.getInt("creditf");
                    if (debug) {
                        System.out.println("prixfixe=" + prixfixe + "/prixm=" + prixm + "/memoire=" + memoire + "/niveau=" + niveau + "/creditf=" + creditf);
                    }
                }

                if (prixfixe) {
                    prix = prixm;
                    if (debug) {
                        System.out.println("avoirmatierePrix--Nomat" + nomat + "prix fixe" + prix);
                    }
                } else {

                    if (memoire != 0) {


                        if (memoire == 1 && n >= 1) {
                            prix = creditf * 50000 / 2;
                        } else if (memoire == 0 && n == 0) {
                            prix = creditf * 50000;
                        }

                        if (memoire == 2 && n >= 2) {
                            prix = creditf * 50000 / 2;
                        } else if (memoire == 2 && n < 2) {
                            prix = creditf * 50000;
                        }
                        if (debug) {
                            System.out.println("avoirmatierePrix--Nomat" + nomat + " fois inscri:" + n + "prix memoire/master=" + memoire + "/prix=" + prix);
                        }
                    } else if (memoire == 0) {

                        if (siancienetd) {

                            switch (niveau) {
                                case 0:
                                    prix = creditf * 20000;
                                case 1:
                                    prix = creditf * 20000;
                                    break;
                                case 2:
                                    prix = creditf * 20000;
                                    break;
                                case 3:
                                    prix = creditf * 50000;
                                    break;

                            }
                            if (debug) {
                                System.out.println("avoirmatierePrix--Nomat" + nomat + "prix pour l'ancien etd" + prix);
                            }
                        } else {
                            switch (niveau) {
                                case 0:
                                    prix = creditf * 25000;
                                case 1:
                                    prix = creditf * 25000;
                                    break;
                                case 2:
                                    prix = creditf * 30000;
                                    break;
                                case 3:
                                    prix = creditf * 60000;
                                    break;

                            }
                            if (debug) {
                                System.out.println("avoirmatierePrix--Nomat" + nomat + "prix nouveau etd" + prix);
                            }


                        }//fin else ,nouveau etd

                    }//fin si mat n'est pas memoire cad matiere normal


                }// si matiere n'est pas prix fixe



            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            connection.close();

        }//fin de if nomat not zero
        else {
            if (debug) {
                System.out.println("avoirmatierePrix--le parametre nomat est zero");
            }

        }
        return prix;
    }

    public int InscriptionFois(int noetud, int nomatiere) throws NamingException, SQLException {

        int n = 0;
        Statement stmt;
        if (noetud != 0 && nomatiere != 0) {
            String query = "select distinct count(*) from Etudier_valeurs as t1,Inscription as t2,Fiche as t3,Etudiant as t4,Matiere as t5 ";
            query += "where t1.Nomatiere=t5.Nomatiere ";
            query += "and t2.NoEtud=t4.NoEtud ";
            query += "and t1.code_etudier=t2.code_etudier ";
            query += "and t1.NoFiche=t3.NoFiche ";
            query += "and t2.code_etudier=t3.code_etudier";
            query += " and (t1.Nomatiere=" + nomatiere + " or t5.substitution=\"";
            query += AvoirCnam(nomatiere);
            query += "\")";
            query += " and t2.NoEtud=" + noetud;
            query += " and t3.payee=1";

            if (debug) {
                System.out.println("inscriptionfois query" + query);
            }

            try {
                connection = getJdbcIsae().getConnection();
                stmt = connection.createStatement();
                ResultSet rec = stmt.executeQuery(query);
                if (rec.first()) {
                    n = rec.getInt("count(*)");

                    if (debug) {
                        System.out.println("inscriptionFois:n=" + n);
                    }
                }

            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
                if (debug) {
                    System.out.println(ex.getLocalizedMessage());
                }


            }
            connection.close();
        } else {
            if (debug) {
                System.out.println("InscriptionFois exception: nomatiere et/ou noetudiant egal a zero");
            }
        }
        return n;
    }

    public String AvoirLangueEtd(int noet) throws NamingException, SQLException {

        String langueE = null;
        Statement stmt;
        String requete = "select langueE from Etudiant ";
        requete += "where NoEtud=" + noet;
        ResultSet rec;
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            rec = stmt.executeQuery(requete);
            if (rec.first()) {
                langueE = rec.getString(1);
            }

            connection.close();
        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();
        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return langueE;
    }

    public boolean MatAnnuelle(int nomat) throws NamingException, SQLException {

        boolean annuelle = false;
        Statement stmt;
        String requete = "select annuelle from Matiere ";
        requete += "where Nomatiere=" + nomat;
        ResultSet rec;
        try {
            connection = getJdbcIsae().getConnection();
            stmt = connection.createStatement();
            rec = stmt.executeQuery(requete);
            if (rec.first()) {
                if (rec.getInt(1) == 1) {
                    annuelle = true;
                }

            }


        } catch (SQLException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            if (debug) {
                System.out.println(ex.getLocalizedMessage());
            }
        }
        connection.close();
        return annuelle;
    }

    public String avoirEmailProfesseur(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select emailp from Professeur where numprof=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public String avoirTelProfesseur(int num) throws ClassNotFoundException {
        Statement stmt;
        String requete = "select telp from Professeur where numprof=" + num;
        ResultSet records;
        String str = "";
        try {
            connection = getJdbcDetail().getConnection();
            stmt = connection.createStatement();
            records = stmt.executeQuery(requete);
            while (records.next()) {
                str = records.getString(1);
            }
            connection.close();

        } catch (SQLException sqlException) {
            try {
                connection.close();
            } catch (SQLException ex) {
                Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
            }
            sqlException.printStackTrace();

        } catch (NamingException ex) {
            Logger.getLogger(InitialQuerys.class.getName()).log(Level.SEVERE, null, ex);
        }
        return str;
    }

    public DataSource getJdbcIsae() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/isae");
    }

    private DataSource getJdbcDetail() throws NamingException {
        Context c = new InitialContext();
        return (DataSource) c.lookup("java:comp/env/jdbc/Detail");
    }
}
